Extending SQL for Decision Support Applications

Extended Abstract The challenge of extending database systems for decision support applications has been the topic of much recent research—a very incomplete list of previous work includes [11, 8, 12, 4, 10, 5]. Yet, there is no generally accepted solution for the problem, which remains a critical one, since the inability of current DBMSs to support data mining applications is well-tested and clearly documented [12]. Our research approach in addressing this difficult problem is motivated by the observation that aggregate functions provide the linchpin for most decision support computations; moreover inductive discovery from large data sets can be viewed as the process of aggregat-ing low level data into statistical summaries of semantic significance. Therefore, the ATLaS system designed at UCLA [2] allows end-users to define new powerful aggregate functions by writing them in SQL. The same mechanism can be used to define new table functions in ATLaS, whose name stands for Aggregate & Table Language and System. ATLaS is the successor of the AXL system described in [15]. These SQL-based native extension mechanisms turn ATLaS into a powerful and flexible system for advanced data-intensive applications, including applications from many domains that are not supported well by current Object-Relational database systems, which still suffer from limited extensibility. In fact, the only extensibility mechanism now provided by Object-Relational systems relies on nonnative datablades—i.e., on external functions defined in a procedural language and imported into SQL. ATLaS is very effective at expressing decision support tasks: we demonstrate this property by the efficient implementation of several functions, such as rollups, datacubes, clas-sifiers, and frequent item sets for association rules [2]. The performance of these functions expressed in ATLaS is typically within 30% of the performance of the same algorithms coded in C/C++. To achieve this level of performance, ATLaS supports various optimization techniques, and the ability of manipulating in-memory tables in SQL. In fact, attributes of reference type in such tables allow the efficient support of data structures, such as tries, that are instrumental in implementing data mining algorithms, such as Apriori [1, 7]. The stream-oriented computation model used by ATLaS contrasts with the computation model based on 'blocking' semantics that is normally used for aggregates in current database systems. Thus, online aggregates [9], time-series queries [13], sliding-window aggregates , approximate aggregates, and continuous queries [3] are naturally supported in ATLaS. Furthermore, important properties of an ATLaS program, such as blocking behavior and monotonicity, …